We are given data from ad sites such as google and facebook, how can these metrics be used to create actionable insights for the rest of the teams? Done in presentation form for ease of process.
ABCJewelry wishes to increase sales while also reducing ad costs. Our job is to find actionable insights to help with those decisions.
# Import
import numpy as np
import pandas as pd
import altair as alt
import pandas_profiling as pp
import matplotlib.pyplot as plt
%matplotlib inline
import seaborn as sns
import warnings # to avoid warnings
warnings.filterwarnings('ignore')
/home/jeffwa/anaconda3/envs/DL_new/lib/python3.10/site-packages/tqdm/auto.py:22: TqdmWarning: IProgress not found. Please update jupyter and ipywidgets. See https://ipywidgets.readthedocs.io/en/stable/user_install.html from .autonotebook import tqdm as notebook_tqdm /tmp/ipykernel_11672/160099804.py:5: DeprecationWarning: `import pandas_profiling` is going to be deprecated by April 1st. Please use `import ydata_profiling` instead. import pandas_profiling as pp
import plotly.graph_objs as go
import plotly.offline as pyo
import plotly.express as px
# display all columns of the dataframe
pd.options.display.max_columns = None
# use below code to convert the 'exponential' values to float
np.set_printoptions(suppress=True)
# set the plot size using 'rcParams'
# once the plot size is set using 'rcParams', it sets the size of all the forthcoming plots in the file
# pass width and height in inches to 'figure.figsize'
plt.rcParams['figure.figsize'] = [15,8]
# load
df=pd.read_csv("dataset.csv",low_memory=False)
# preview 5 first 5 rows
df.head(5)
| campaign_item_id | no_of_days | time | ext_service_id | ext_service_name | creative_id | creative_width | creative_height | search_tags | template_id | landing_page | advertiser_id | advertiser_name | network_id | approved_budget | advertiser_currency | channel_id | channel_name | max_bid_cpm | network_margin | campaign_budget_usd | impressions | clicks | stats_currency | currency_code | exchange_rate | media_cost_usd | position_in_content | unique_reach | total_reach | search_tag_cat | cmi_currency_code | timezone | weekday_cat | keywords | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2733 | 7 | 2022-05-01 | 128 | Facebook Ads | 1000 | 300.0 | 250.0 | #The Power of X | 90.0 | https://www.abcjewelry.com/collections/boho-je... | 4756 | Web | 190 | 400.0 | SGD | 32 | Mobile | NaN | 0.0 | 652.173913 | 837 | 8 | SGD | SGD | 1 | 14.058514 | NaN | NaN | NaN | Others | SGD | Asia/Singapore | week_end | delicate bracelets |
| 1 | 2733 | 8 | 2022-05-02 | 16 | DV360 | 1000 | 300.0 | 250.0 | #Be Bold. Be X | 90.0 | https://www.abcjewelry.com/women/costume-jewelry/ | 4756 | Web | 190 | 400.0 | SGD | 8 | Social | NaN | 0.0 | 652.173913 | 2634 | 44 | SGD | SGD | 1 | 99.633496 | NaN | NaN | NaN | Others | SGD | Asia/Singapore | week_day | summer jewelry |
| 2 | 2733 | 9 | 2022-05-03 | 128 | Facebook Ads | 1000 | 300.0 | 250.0 | #Embrace Your Individuality with X | 90.0 | https://www.abcjewelry.com/collections/boho-je... | 4756 | Web | 190 | 400.0 | SGD | 8 | Social | NaN | 0.0 | 652.173913 | 2135 | 32 | SGD | SGD | 1 | 109.419677 | NaN | NaN | NaN | Others | SGD | Asia/Singapore | week_day | artisan jewelry |
| 3 | 2733 | 10 | 2022-05-04 | 128 | Facebook Ads | 1000 | 300.0 | 250.0 | #Be Bold. Be X | 90.0 | https://www.abcjewelry.com/collections/rhinest... | 4756 | Web | 190 | 400.0 | SGD | 64 | Video | NaN | 0.0 | 652.173913 | 2327 | 48 | SGD | SGD | 1 | 115.209499 | NaN | NaN | NaN | Others | SGD | Asia/Singapore | week_day | layered bracelets |
| 4 | 2733 | 11 | 2022-05-05 | 4 | Google Ads | 1000 | 300.0 | 250.0 | #Be Bold. Be X | 90.0 | https://www.abcjewelry.com/collections/boho-je... | 4756 | Web | 190 | 400.0 | SGD | 32 | Mobile | NaN | 0.0 | 652.173913 | 1538 | 20 | SGD | SGD | 1 | 66.990104 | NaN | NaN | NaN | Others | SGD | Asia/Singapore | week_day | minimalist jewelry |
# see total number of rows
df.shape
(72612, 35)
df.columns
Index(['campaign_item_id', 'no_of_days', 'time', 'ext_service_id',
'ext_service_name', 'creative_id', 'creative_width', 'creative_height',
'search_tags', 'template_id', 'landing_page', 'advertiser_id',
'advertiser_name', 'network_id', 'approved_budget',
'advertiser_currency', 'channel_id', 'channel_name', 'max_bid_cpm',
'network_margin', 'campaign_budget_usd', 'impressions', 'clicks',
'stats_currency', 'currency_code', 'exchange_rate', 'media_cost_usd',
'position_in_content', 'unique_reach', 'total_reach', 'search_tag_cat',
'cmi_currency_code', 'timezone', 'weekday_cat', 'keywords'],
dtype='object')
df.info()
<class 'pandas.core.frame.DataFrame'> RangeIndex: 72612 entries, 0 to 72611 Data columns (total 35 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 campaign_item_id 72612 non-null int64 1 no_of_days 72612 non-null int64 2 time 72612 non-null object 3 ext_service_id 72612 non-null int64 4 ext_service_name 72612 non-null object 5 creative_id 72612 non-null int64 6 creative_width 69200 non-null float64 7 creative_height 69200 non-null float64 8 search_tags 72612 non-null object 9 template_id 69200 non-null float64 10 landing_page 72612 non-null object 11 advertiser_id 72612 non-null int64 12 advertiser_name 72612 non-null object 13 network_id 72612 non-null int64 14 approved_budget 72206 non-null float64 15 advertiser_currency 72612 non-null object 16 channel_id 72612 non-null int64 17 channel_name 72612 non-null object 18 max_bid_cpm 7406 non-null float64 19 network_margin 72612 non-null float64 20 campaign_budget_usd 72612 non-null float64 21 impressions 72612 non-null int64 22 clicks 72612 non-null int64 23 stats_currency 72612 non-null object 24 currency_code 72612 non-null object 25 exchange_rate 72612 non-null int64 26 media_cost_usd 72612 non-null float64 27 position_in_content 0 non-null float64 28 unique_reach 0 non-null float64 29 total_reach 0 non-null float64 30 search_tag_cat 72612 non-null object 31 cmi_currency_code 72612 non-null object 32 timezone 72612 non-null object 33 weekday_cat 72612 non-null object 34 keywords 72612 non-null object dtypes: float64(11), int64(10), object(14) memory usage: 19.4+ MB
# sort the variables on the basis of total null values in the variable
Total = df.isnull().sum().sort_values(ascending = False)
#calculate nulls
Percent = (df.isnull().sum()*100/df.isnull().count()).sort_values(ascending = False)
missing_data = pd.concat([Total, Percent], axis = 1, keys = ['Total', 'Percentage of Missing Values'])
# add the column containing data type of each variable
missing_data['Type'] = df[missing_data.index].dtypes
missing_data
| Total | Percentage of Missing Values | Type | |
|---|---|---|---|
| unique_reach | 72612 | 100.000000 | float64 |
| total_reach | 72612 | 100.000000 | float64 |
| position_in_content | 72612 | 100.000000 | float64 |
| max_bid_cpm | 65206 | 89.800584 | float64 |
| creative_width | 3412 | 4.698948 | float64 |
| creative_height | 3412 | 4.698948 | float64 |
| template_id | 3412 | 4.698948 | float64 |
| approved_budget | 406 | 0.559136 | float64 |
| exchange_rate | 0 | 0.000000 | int64 |
| clicks | 0 | 0.000000 | int64 |
| stats_currency | 0 | 0.000000 | object |
| currency_code | 0 | 0.000000 | object |
| campaign_item_id | 0 | 0.000000 | int64 |
| media_cost_usd | 0 | 0.000000 | float64 |
| campaign_budget_usd | 0 | 0.000000 | float64 |
| search_tag_cat | 0 | 0.000000 | object |
| cmi_currency_code | 0 | 0.000000 | object |
| timezone | 0 | 0.000000 | object |
| weekday_cat | 0 | 0.000000 | object |
| impressions | 0 | 0.000000 | int64 |
| channel_name | 0 | 0.000000 | object |
| network_margin | 0 | 0.000000 | float64 |
| no_of_days | 0 | 0.000000 | int64 |
| channel_id | 0 | 0.000000 | int64 |
| advertiser_currency | 0 | 0.000000 | object |
| network_id | 0 | 0.000000 | int64 |
| advertiser_name | 0 | 0.000000 | object |
| advertiser_id | 0 | 0.000000 | int64 |
| landing_page | 0 | 0.000000 | object |
| search_tags | 0 | 0.000000 | object |
| creative_id | 0 | 0.000000 | int64 |
| ext_service_name | 0 | 0.000000 | object |
| ext_service_id | 0 | 0.000000 | int64 |
| time | 0 | 0.000000 | object |
| keywords | 0 | 0.000000 | object |
# creative width
df['creative_width'] = df['creative_width'].fillna(0)
# creative height
df['creative_height'] = df['creative_height'].fillna(0)
# template id
df['template_id'] = df['template_id'].fillna(-1)
# approved_budget
df['approved_budget'] = df['approved_budget'].fillna(0)
df.drop(columns=['position_in_content','unique_reach','total_reach','max_bid_cpm'],inplace=True)
df.describe()
| campaign_item_id | no_of_days | ext_service_id | creative_id | creative_width | creative_height | template_id | advertiser_id | network_id | approved_budget | channel_id | network_margin | campaign_budget_usd | impressions | clicks | exchange_rate | media_cost_usd | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| count | 72612.000000 | 72612.000000 | 72612.000000 | 72612.000000 | 72612.000000 | 72612.000000 | 72612.000000 | 72612.000000 | 72612.000000 | 7.261200e+04 | 72612.000000 | 72612.0 | 72612.000000 | 72612.000000 | 72612.000000 | 72612.0 | 72612.000000 |
| mean | 3130.143282 | 27.036344 | 49.449127 | 7450.124842 | 255.226409 | 212.688674 | 79.131659 | 6195.862213 | 345.272861 | 1.251030e+05 | 21.847780 | 0.0 | 617.630284 | 1370.161847 | 52.696386 | 1.0 | 11.819999 |
| std | 142.154918 | 25.479175 | 55.881324 | 4062.384982 | 106.899767 | 89.083139 | 27.033401 | 387.864576 | 23.707191 | 5.611237e+05 | 23.759286 | 0.0 | 1354.606619 | 3702.699962 | 377.308103 | 0.0 | 48.370659 |
| min | 2733.000000 | 0.000000 | 4.000000 | 1000.000000 | 0.000000 | 0.000000 | -1.000000 | 4756.000000 | 188.000000 | 0.000000e+00 | 1.000000 | 0.0 | 2.452316 | 511.000000 | 2.000000 | 1.0 | 0.000000 |
| 25% | 3148.000000 | 9.000000 | 4.000000 | 3725.000000 | 300.000000 | 250.000000 | 90.000000 | 6319.000000 | 353.000000 | 6.000000e+03 | 4.000000 | 0.0 | 205.994550 | 526.000000 | 5.000000 | 1.0 | 0.435791 |
| 50% | 3173.000000 | 19.000000 | 16.000000 | 7855.000000 | 300.000000 | 250.000000 | 90.000000 | 6385.000000 | 353.000000 | 1.000000e+04 | 8.000000 | 0.0 | 377.656676 | 577.000000 | 8.000000 | 1.0 | 1.709001 |
| 75% | 3202.000000 | 37.000000 | 128.000000 | 10995.000000 | 300.000000 | 250.000000 | 90.000000 | 6394.000000 | 353.000000 | 1.500000e+04 | 32.000000 | 0.0 | 755.313351 | 816.000000 | 13.000000 | 1.0 | 8.276369 |
| max | 3960.000000 | 118.000000 | 128.000000 | 15605.000000 | 300.000000 | 250.000000 | 93.000000 | 6490.000000 | 353.000000 | 6.000000e+06 | 64.000000 | 0.0 | 39559.896155 | 153959.000000 | 31807.000000 | 1.0 | 2295.028945 |
# summary of categorical variables
df.describe(include=object)
# Note: If we pass 'include=object' to the .describe(), it will return descriptive statistics for categorical variables only
| time | ext_service_name | search_tags | landing_page | advertiser_name | advertiser_currency | channel_name | stats_currency | currency_code | search_tag_cat | cmi_currency_code | timezone | weekday_cat | keywords | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| count | 72612 | 72612 | 72612 | 72612 | 72612 | 72612 | 72612 | 72612 | 72612 | 72612 | 72612 | 72612 | 72612 | 72612 |
| unique | 224 | 3 | 6 | 45 | 44 | 5 | 5 | 5 | 5 | 5 | 5 | 7 | 2 | 118 |
| top | 2022-10-22 | Facebook Ads | #The Ultimate Fashion Statement with X | https://www.abcjewelry.com/collections/boho-je... | Oman | AED | Mobile | AED | AED | Others | AED | Asia/Kolkata | week_day | beaded jewelry |
| freq | 955 | 24275 | 12293 | 1684 | 8641 | 53661 | 14625 | 53661 | 53661 | 49008 | 53661 | 64264 | 62424 | 674 |
df['ctr']=(df['clicks']/df['impressions'])*100
df.loc[:,:].sort_values('ctr',ascending=False)[1000:1050]
| campaign_item_id | no_of_days | time | ext_service_id | ext_service_name | creative_id | creative_width | creative_height | search_tags | template_id | landing_page | advertiser_id | advertiser_name | network_id | approved_budget | advertiser_currency | channel_id | channel_name | network_margin | campaign_budget_usd | impressions | clicks | stats_currency | currency_code | exchange_rate | media_cost_usd | search_tag_cat | cmi_currency_code | timezone | weekday_cat | keywords | ctr | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 16117 | 2977 | 2 | 2022-08-27 | 16 | DV360 | 3145 | 300.0 | 250.0 | #The Power of X | 90.0 | https://www.abcjewelry.com/collections | 6112 | Coimbatore | 353 | 15000.0 | INR | 64 | Video | 0.0 | 247.005064 | 1504 | 98 | INR | INR | 1 | 4.650439 | Others | INR | Asia/Kolkata | week_day | body jewelry | 6.515957 |
| 68678 | 3543 | 34 | 2022-05-19 | 4 | Google Ads | 13525 | 300.0 | 250.0 | #Embrace Your Individuality with X | 90.0 | https://www.abcjewelry.com/collections/chunky-... | 6370 | North | 353 | 5000.0 | INR | 8 | Social | 0.0 | 9812.339016 | 13923 | 907 | INR | INR | 1 | 174.517128 | Others | INR | Asia/Kolkata | week_day | dazzling jewelry | 6.514401 |
| 21303 | 3128 | 10 | 2022-09-02 | 4 | Google Ads | 4625 | 300.0 | 250.0 | #The Power of X | 93.0 | https://www.abcjewelry.com/women/bold-jewelry/ | 6331 | Tamil Nadu | 353 | 870000.0 | INR | 4 | Search | 0.0 | 441.982072 | 5965 | 388 | INR | INR | 1 | 66.840845 | Retargeting | INR | Asia/Kolkata | week_day | renaissance jewelry | 6.504610 |
| 70357 | 3055 | 24 | 2022-10-25 | 4 | Google Ads | 14265 | 0.0 | 0.0 | #The Ultimate Fashion Statement with X | -1.0 | https://www.abcjewelry.com/collections/stateme... | 6331 | Tamil Nadu | 353 | 268786.0 | INR | 4 | Search | 0.0 | 11827.689243 | 24953 | 1622 | INR | INR | 1 | 360.303654 | Others | INR | Asia/Kolkata | week_day | cuff bracelets | 6.500220 |
| 21196 | 3128 | 24 | 2022-09-11 | 128 | Facebook Ads | 4590 | 300.0 | 250.0 | #Embrace Your Individuality with X | 90.0 | https://www.abcjewelry.com/collections/costume... | 6331 | Tamil Nadu | 353 | 870000.0 | INR | 64 | Video | 0.0 | 2241.035857 | 6155 | 400 | INR | INR | 1 | 74.365122 | Others | INR | Asia/Kolkata | week_end | layered bracelets | 6.498781 |
| 15871 | 2949 | 17 | 2022-10-11 | 4 | Google Ads | 3070 | 300.0 | 250.0 | #The Power of X | 90.0 | https://www.abcjewelry.com/collections/mixed-m... | 5908 | Addis Ababa | 353 | 160000.0 | INR | 32 | Mobile | 0.0 | 982.921735 | 1139 | 74 | INR | INR | 1 | 7.205023 | Others | INR | Asia/Kolkata | week_day | dangle earrings | 6.496927 |
| 72476 | 3533 | 3 | 2022-11-20 | 4 | Google Ads | 15570 | 0.0 | 0.0 | #The X Factor - Fashion for the Fearless | -1.0 | https://www.abcjewelry.com/women/fashion-jewelry/ | 6307 | Punjab | 353 | 163135.0 | INR | 4 | Search | 0.0 | 306.635594 | 832 | 54 | INR | INR | 1 | 3.380317 | Others | INR | Asia/Kolkata | week_end | body chains | 6.490385 |
| 18930 | 3051 | 38 | 2022-09-25 | 128 | Facebook Ads | 3945 | 300.0 | 250.0 | #The Ultimate Fashion Statement with X | 90.0 | https://www.abcjewelry.com/collections/mixed-m... | 5641 | Karnataka | 353 | 5000.0 | INR | 64 | Video | 0.0 | 1792.828685 | 4623 | 300 | INR | INR | 1 | 72.674296 | Retargeting | INR | Asia/Kolkata | week_end | zodiac jewelry | 6.489293 |
| 19279 | 3054 | 32 | 2022-08-23 | 128 | Facebook Ads | 4045 | 300.0 | 250.0 | #The Power of X | 90.0 | https://www.abcjewelry.com/collections/afforda... | 5641 | Karnataka | 353 | 5000.0 | INR | 64 | Video | 0.0 | 1677.041833 | 4207 | 273 | INR | INR | 1 | 32.624534 | Others | INR | Asia/Kolkata | week_day | handcrafted jewelry | 6.489185 |
| 20248 | 3103 | 12 | 2022-10-10 | 4 | Google Ads | 4330 | 300.0 | 250.0 | #Timeless X Style | 90.0 | https://www.abcjewelry.com/collections/beaded-... | 6370 | North | 353 | 100000.0 | INR | 8 | Social | 0.0 | 3090.616887 | 11698 | 759 | INR | INR | 1 | 118.416547 | Others | INR | Asia/Kolkata | week_day | bridal jewelry | 6.488289 |
| 70343 | 3055 | 10 | 2022-10-11 | 4 | Google Ads | 14265 | 0.0 | 0.0 | #The X Factor - Fashion for the Fearless | -1.0 | https://www.abcjewelry.com/collections/stateme... | 6331 | Tamil Nadu | 353 | 268786.0 | INR | 4 | Search | 0.0 | 11827.689243 | 34263 | 2222 | INR | INR | 1 | 497.550045 | Others | INR | Asia/Kolkata | week_day | whimsical jewelry | 6.485130 |
| 69691 | 3049 | 25 | 2022-10-25 | 16 | DV360 | 13965 | 0.0 | 0.0 | #Timeless X Style | -1.0 | https://www.abcjewelry.com/collections/chunky-... | 6298 | Gujarat | 353 | 0.0 | INR | 32 | Mobile | 0.0 | 8715.139442 | 18027 | 1169 | INR | INR | 1 | 263.858285 | Others | INR | Asia/Kolkata | week_day | birthstone jewelry | 6.484717 |
| 7412 | 2802 | 63 | 2022-09-01 | 16 | DV360 | 1740 | 0.0 | 0.0 | #Timeless X Style | 23.0 | https://www.abcjewelry.com/collections/rhinest... | 5395 | Luxor | 191 | 500000.0 | EGP | 4 | Search | 0.0 | 18.093487 | 4426 | 287 | EGP | EGP | 1 | 18.383460 | Others | EGP | Africa/Cairo | week_day | layered earrings | 6.484410 |
| 18729 | 3048 | 14 | 2022-10-12 | 16 | DV360 | 3890 | 300.0 | 250.0 | #The Power of X | 90.0 | https://www.abcjewelry.com/collections/cuff-br... | 6298 | Gujarat | 353 | 0.0 | INR | 32 | Mobile | 0.0 | 1867.529880 | 5815 | 377 | INR | INR | 1 | 71.890183 | Others | INR | Asia/Kolkata | week_day | mixed metal jewelry | 6.483233 |
| 70867 | 3104 | 18 | 2022-10-19 | 128 | Facebook Ads | 14455 | 0.0 | 0.0 | #The Ultimate Fashion Statement with X | -1.0 | https://www.abcjewelry.com/women/costume-jewelry/ | 6370 | North | 353 | 100000.0 | INR | 8 | Social | 0.0 | 39559.896155 | 98968 | 6416 | INR | INR | 1 | 1325.016718 | Others | INR | Asia/Kolkata | week_day | artisanal jewelry | 6.482904 |
| 70342 | 3055 | 9 | 2022-10-10 | 128 | Facebook Ads | 14265 | 0.0 | 0.0 | #Be Bold. Be X | -1.0 | https://www.abcjewelry.com/collections/unique-... | 6331 | Tamil Nadu | 353 | 268786.0 | INR | 4 | Search | 0.0 | 11827.689243 | 16806 | 1089 | INR | INR | 1 | 240.213963 | Others | INR | Asia/Kolkata | week_day | layered jewelry | 6.479829 |
| 19412 | 3054 | 8 | 2022-09-14 | 4 | Google Ads | 4085 | 300.0 | 250.0 | #Embrace Your Individuality with X | 90.0 | https://www.abcjewelry.com/women/handmade-jewe... | 5641 | Karnataka | 353 | 5000.0 | INR | 32 | Mobile | 0.0 | 622.509960 | 5525 | 358 | INR | INR | 1 | 31.954743 | Others | INR | Asia/Kolkata | week_day | chunky jewelry | 6.479638 |
| 69711 | 3049 | 18 | 2022-10-19 | 128 | Facebook Ads | 13970 | 0.0 | 0.0 | #Embrace Your Individuality with X | -1.0 | https://www.abcjewelry.com/women/statement-jew... | 6298 | Gujarat | 353 | 0.0 | INR | 32 | Mobile | 0.0 | 622.509960 | 2732 | 177 | INR | INR | 1 | 26.514287 | Others | INR | Asia/Kolkata | week_day | multi-strand necklaces | 6.478770 |
| 19351 | 3054 | 31 | 2022-09-18 | 128 | Facebook Ads | 4065 | 300.0 | 250.0 | #The Ultimate Fashion Statement with X | 90.0 | https://www.abcjewelry.com/women/designer-insp... | 5641 | Karnataka | 353 | 5000.0 | INR | 8 | Social | 0.0 | 2241.035857 | 8584 | 556 | INR | INR | 1 | 75.919705 | Others | INR | Asia/Kolkata | week_end | art nouveau jewelry | 6.477167 |
| 69685 | 3049 | 19 | 2022-10-19 | 16 | DV360 | 13965 | 0.0 | 0.0 | #The Power of X | -1.0 | https://www.abcjewelry.com/women/statement-jew... | 6298 | Gujarat | 353 | 0.0 | INR | 64 | Video | 0.0 | 8715.139442 | 19256 | 1247 | INR | INR | 1 | 282.374345 | Others | INR | Asia/Kolkata | week_day | pearl jewelry | 6.475904 |
| 7396 | 2802 | 46 | 2022-08-15 | 16 | DV360 | 1740 | 0.0 | 0.0 | #The X Factor - Fashion for the Fearless | 23.0 | https://www.abcjewelry.com/women/handmade-jewe... | 5395 | Luxor | 191 | 125000.0 | EGP | 8 | Social | 0.0 | 18.093487 | 4126 | 267 | EGP | EGP | 1 | 17.582524 | Others | EGP | Africa/Cairo | week_day | rhinestone jewelry | 6.471159 |
| 69705 | 3049 | 12 | 2022-10-13 | 4 | Google Ads | 13970 | 0.0 | 0.0 | #The Ultimate Fashion Statement with X | -1.0 | https://www.abcjewelry.com/women/unique-jewelry/ | 6298 | Gujarat | 353 | 0.0 | INR | 8 | Social | 0.0 | 622.509960 | 2658 | 172 | INR | INR | 1 | 26.521432 | Others | INR | Asia/Kolkata | week_day | mixed metal jewelry | 6.471031 |
| 18976 | 3051 | 6 | 2022-09-11 | 4 | Google Ads | 3965 | 300.0 | 250.0 | #Be Bold. Be X | 93.0 | https://www.abcjewelry.com/collections/stateme... | 5641 | Karnataka | 353 | 5000.0 | INR | 1 | Display | 0.0 | 498.007968 | 3091 | 200 | INR | INR | 1 | 24.771378 | Retargeting | INR | Asia/Kolkata | week_end | chic and affordable jewelry | 6.470398 |
| 17198 | 3050 | 14 | 2022-09-07 | 16 | DV360 | 3465 | 300.0 | 250.0 | #The Power of X | 90.0 | https://www.abcjewelry.com/collections/gemston... | 6337 | Kuala Lumpur | 353 | 268000.0 | INR | 64 | Video | 0.0 | 4980.079681 | 79861 | 5166 | INR | INR | 1 | 235.481599 | Others | INR | Asia/Kolkata | week_day | statement rings | 6.468739 |
| 19621 | 3058 | 9 | 2022-10-07 | 4 | Google Ads | 4135 | 300.0 | 250.0 | #The X Factor - Fashion for the Fearless | 90.0 | https://www.abcjewelry.com/collections/mixed-m... | 6319 | Orissa | 353 | 215000.0 | INR | 8 | Social | 0.0 | 1494.023904 | 5148 | 333 | INR | INR | 1 | 57.216612 | Others | INR | Asia/Kolkata | week_day | art nouveau jewelry | 6.468531 |
| 22368 | 3134 | 21 | 2022-09-08 | 16 | DV360 | 4940 | 300.0 | 250.0 | #The X Factor - Fashion for the Fearless | 90.0 | https://www.abcjewelry.com/collections/stateme... | 6319 | Orissa | 353 | 415000.0 | INR | 8 | Social | 0.0 | 1245.019920 | 5442 | 352 | INR | INR | 1 | 39.140953 | Others | INR | Asia/Kolkata | week_day | beaded bracelets | 6.468210 |
| 69678 | 3049 | 12 | 2022-10-12 | 128 | Facebook Ads | 13965 | 0.0 | 0.0 | #Embrace Your Individuality with X | -1.0 | https://www.abcjewelry.com/collections/tassel-... | 6298 | Gujarat | 353 | 0.0 | INR | 4 | Search | 0.0 | 8715.139442 | 24480 | 1583 | INR | INR | 1 | 361.062937 | Others | INR | Asia/Kolkata | week_day | hair accessories | 6.466503 |
| 69725 | 3049 | 6 | 2022-10-27 | 4 | Google Ads | 13975 | 0.0 | 0.0 | #Timeless X Style | -1.0 | https://www.abcjewelry.com/collections/beaded-... | 6298 | Gujarat | 353 | 0.0 | INR | 1 | Display | 0.0 | 622.509960 | 8695 | 562 | INR | INR | 1 | 43.544909 | Others | INR | Asia/Kolkata | week_day | initial jewelry | 6.463485 |
| 16532 | 3035 | 8 | 2022-10-06 | 16 | DV360 | 3285 | 300.0 | 250.0 | #The X Factor - Fashion for the Fearless | 90.0 | https://www.abcjewelry.com/collections/stateme... | 6292 | Andhra Pradesh | 353 | 205000.0 | INR | 32 | Mobile | 0.0 | 2490.039841 | 16863 | 1089 | INR | INR | 1 | 275.329567 | Others | INR | Asia/Kolkata | week_day | luxury jewelry | 6.457926 |
| 7411 | 2802 | 62 | 2022-08-31 | 128 | Facebook Ads | 1740 | 0.0 | 0.0 | #Timeless X Style | 23.0 | https://www.abcjewelry.com/collections/dainty-... | 5395 | Luxor | 191 | 500000.0 | EGP | 64 | Video | 0.0 | 18.093487 | 3887 | 251 | EGP | EGP | 1 | 17.277772 | Others | EGP | Africa/Cairo | week_day | rhinestone jewelry | 6.457422 |
| 19150 | 3054 | 15 | 2022-08-24 | 128 | Facebook Ads | 4010 | 300.0 | 250.0 | #Timeless X Style | 93.0 | https://www.abcjewelry.com/collections/boho-je... | 5641 | Karnataka | 353 | 5000.0 | INR | 4 | Search | 0.0 | 249.003984 | 2323 | 150 | INR | INR | 1 | 17.683272 | Retargeting | INR | Asia/Kolkata | week_day | dangle earrings | 6.457167 |
| 70340 | 3055 | 7 | 2022-10-08 | 16 | DV360 | 14265 | 0.0 | 0.0 | #The Ultimate Fashion Statement with X | -1.0 | https://www.abcjewelry.com/collections/unique-... | 6331 | Tamil Nadu | 353 | 268786.0 | INR | 32 | Mobile | 0.0 | 11827.689243 | 25988 | 1678 | INR | INR | 1 | 375.560319 | Others | INR | Asia/Kolkata | week_day | unique jewelry | 6.456826 |
| 18275 | 3046 | 10 | 2022-09-02 | 16 | DV360 | 3765 | 300.0 | 250.0 | #The Power of X | 93.0 | https://www.abcjewelry.com/women/fashion-jewelry | 6298 | Gujarat | 353 | 0.0 | INR | 4 | Search | 0.0 | 367.280876 | 6134 | 396 | INR | INR | 1 | 57.184589 | Retargeting | INR | Asia/Kolkata | week_day | body jewelry | 6.455820 |
| 20849 | 3130 | 11 | 2022-09-04 | 4 | Google Ads | 4490 | 300.0 | 250.0 | #The Ultimate Fashion Statement with X | 93.0 | https://www.abcjewelry.com/women/designer-insp... | 6334 | Thailand | 353 | 1010000.0 | INR | 64 | Video | 0.0 | 423.306773 | 6739 | 435 | INR | INR | 1 | 63.892121 | Retargeting | INR | Asia/Kolkata | week_end | drop earrings | 6.454964 |
| 16531 | 3035 | 7 | 2022-10-05 | 4 | Google Ads | 3285 | 300.0 | 250.0 | #The Power of X | 90.0 | https://www.abcjewelry.com/collections/trendy-... | 6292 | Andhra Pradesh | 353 | 205000.0 | INR | 32 | Mobile | 0.0 | 2490.039841 | 15959 | 1030 | INR | INR | 1 | 275.793563 | Others | INR | Asia/Kolkata | week_day | baroque jewelry | 6.454038 |
| 7390 | 2802 | 40 | 2022-08-09 | 128 | Facebook Ads | 1740 | 0.0 | 0.0 | #Embrace Your Individuality with X | 23.0 | https://www.abcjewelry.com/collections/beaded-... | 5395 | Luxor | 191 | 125000.0 | EGP | 64 | Video | 0.0 | 18.093487 | 4649 | 300 | EGP | EGP | 1 | 18.996798 | Others | EGP | Africa/Cairo | week_day | tribal jewelry | 6.453001 |
| 70351 | 3055 | 18 | 2022-10-19 | 16 | DV360 | 14265 | 0.0 | 0.0 | #The Ultimate Fashion Statement with X | -1.0 | https://www.abcjewelry.com/collections | 6331 | Tamil Nadu | 353 | 268786.0 | INR | 8 | Social | 0.0 | 11827.689243 | 26750 | 1724 | INR | INR | 1 | 386.790570 | Others | INR | Asia/Kolkata | week_day | chunky jewelry | 6.444860 |
| 69673 | 3049 | 7 | 2022-10-07 | 4 | Google Ads | 13965 | 0.0 | 0.0 | #Timeless X Style | -1.0 | https://www.abcjewelry.com/women/bold-jewelry/ | 6298 | Gujarat | 353 | 0.0 | INR | 32 | Mobile | 0.0 | 8715.139442 | 21785 | 1403 | INR | INR | 1 | 320.468509 | Others | INR | Asia/Kolkata | week_day | victorian jewelry | 6.440211 |
| 71122 | 3128 | 0 | 2022-08-18 | 4 | Google Ads | 14560 | 0.0 | 0.0 | #The Power of X | -1.0 | https://www.abcjewelry.com/collections/unique-... | 6331 | Tamil Nadu | 353 | 870000.0 | INR | 1 | Display | 0.0 | 1792.828685 | 2907 | 187 | INR | INR | 1 | 30.092506 | Others | INR | Asia/Kolkata | week_day | formal jewelry | 6.432749 |
| 69822 | 3050 | 21 | 2022-10-22 | 128 | Facebook Ads | 14010 | 0.0 | 0.0 | #The Ultimate Fashion Statement with X | -1.0 | https://www.abcjewelry.com/women/trendy-jewelry/ | 5641 | Karnataka | 353 | 805000.0 | INR | 32 | Mobile | 0.0 | 22410.358566 | 38101 | 2449 | INR | INR | 1 | 670.896561 | Others | INR | Asia/Kolkata | week_day | multi-strand necklaces | 6.427653 |
| 70415 | 3056 | 23 | 2022-10-24 | 16 | DV360 | 14280 | 0.0 | 0.0 | #The Ultimate Fashion Statement with X | -1.0 | https://www.abcjewelry.com/collections/layered... | 5686 | Maharashtra | 353 | 1015000.0 | INR | 8 | Social | 0.0 | 622.509960 | 2552 | 164 | INR | INR | 1 | 27.027827 | Others | INR | Asia/Kolkata | week_day | formal jewelry | 6.426332 |
| 16540 | 3035 | 16 | 2022-10-14 | 16 | DV360 | 3285 | 300.0 | 250.0 | #The Ultimate Fashion Statement with X | 90.0 | https://www.abcjewelry.com/collections/vintage... | 6292 | Andhra Pradesh | 353 | 205000.0 | INR | 32 | Mobile | 0.0 | 2490.039841 | 8342 | 536 | INR | INR | 1 | 97.199157 | Others | INR | Asia/Kolkata | week_day | dazzling jewelry | 6.425318 |
| 15796 | 2949 | 14 | 2022-11-10 | 128 | Facebook Ads | 3045 | 300.0 | 250.0 | #Embrace Your Individuality with X | 90.0 | https://www.abcjewelry.com/collections/minimal... | 5908 | Addis Ababa | 353 | 1000.0 | INR | 8 | Social | 0.0 | 1228.652169 | 3145 | 202 | INR | INR | 1 | 31.389407 | Others | INR | Asia/Kolkata | week_day | trendy jewelry | 6.422893 |
| 15890 | 2949 | 19 | 2022-10-13 | 16 | DV360 | 3075 | 300.0 | 250.0 | #Embrace Your Individuality with X | 90.0 | https://www.abcjewelry.com/women/designer-insp... | 5908 | Addis Ababa | 353 | 160000.0 | INR | 8 | Social | 0.0 | 982.921735 | 2274 | 146 | INR | INR | 1 | 22.230288 | Others | INR | Asia/Kolkata | week_day | handmade jewelry | 6.420405 |
| 70411 | 3056 | 19 | 2022-10-20 | 128 | Facebook Ads | 14280 | 0.0 | 0.0 | #Be Bold. Be X | -1.0 | https://www.abcjewelry.com/women/designer-insp... | 5686 | Maharashtra | 353 | 1015000.0 | INR | 8 | Social | 0.0 | 622.509960 | 2430 | 156 | INR | INR | 1 | 25.013629 | Others | INR | Asia/Kolkata | week_day | unique and trendy jewelry | 6.419753 |
| 19631 | 3058 | 19 | 2022-10-17 | 4 | Google Ads | 4135 | 300.0 | 250.0 | #The Power of X | 90.0 | https://www.abcjewelry.com/collections/beaded-... | 6319 | Orissa | 353 | 215000.0 | INR | 4 | Search | 0.0 | 1494.023904 | 5500 | 353 | INR | INR | 1 | 57.680075 | Others | INR | Asia/Kolkata | week_day | huggie earrings | 6.418182 |
| 18727 | 3048 | 12 | 2022-10-10 | 4 | Google Ads | 3890 | 300.0 | 250.0 | #Timeless X Style | 90.0 | https://www.abcjewelry.com/collections/minimal... | 6298 | Gujarat | 353 | 0.0 | INR | 4 | Search | 0.0 | 1867.529880 | 8656 | 555 | INR | INR | 1 | 71.521836 | Others | INR | Asia/Kolkata | week_day | renaissance jewelry | 6.411738 |
| 70356 | 3055 | 23 | 2022-10-24 | 16 | DV360 | 14265 | 0.0 | 0.0 | #The Power of X | -1.0 | https://www.abcjewelry.com/women/bold-jewelry/ | 6331 | Tamil Nadu | 353 | 268786.0 | INR | 4 | Search | 0.0 | 11827.689243 | 25623 | 1640 | INR | INR | 1 | 370.182789 | Others | INR | Asia/Kolkata | week_day | affordable jewelry | 6.400500 |
| 70350 | 3055 | 17 | 2022-10-18 | 128 | Facebook Ads | 14265 | 0.0 | 0.0 | #Be Bold. Be X | -1.0 | https://www.abcjewelry.com/collections/bold-je... | 6331 | Tamil Nadu | 353 | 268786.0 | INR | 4 | Search | 0.0 | 11827.689243 | 26723 | 1710 | INR | INR | 1 | 386.405183 | Others | INR | Asia/Kolkata | week_day | victorian jewelry | 6.398982 |
| 19215 | 3054 | 13 | 2022-09-05 | 16 | DV360 | 4030 | 300.0 | 250.0 | #The X Factor - Fashion for the Fearless | 93.0 | https://www.abcjewelry.com/women/boho-jewelry/ | 5641 | Karnataka | 353 | 5000.0 | INR | 64 | Video | 0.0 | 560.258964 | 8189 | 524 | INR | INR | 1 | 74.654203 | Others | INR | Asia/Kolkata | week_day | vintage-inspired jewelry | 6.398828 |
df.loc[:,:].sort_values('clicks',ascending=False)[1000:1050]
| campaign_item_id | no_of_days | time | ext_service_id | ext_service_name | creative_id | creative_width | creative_height | search_tags | template_id | landing_page | advertiser_id | advertiser_name | network_id | approved_budget | advertiser_currency | channel_id | channel_name | network_margin | campaign_budget_usd | impressions | clicks | stats_currency | currency_code | exchange_rate | media_cost_usd | search_tag_cat | cmi_currency_code | timezone | weekday_cat | keywords | ctr | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 17346 | 3052 | 38 | 2022-08-29 | 16 | DV360 | 3510 | 300.0 | 250.0 | #The X Factor - Fashion for the Fearless | 90.0 | https://www.abcjewelry.com/women/handmade-jewe... | 6337 | Kuala Lumpur | 353 | 1015000.0 | INR | 4 | Search | 0.0 | 1613.545817 | 4051 | 528 | INR | INR | 1 | 42.094553 | Others | INR | Asia/Kolkata | week_day | dangle earrings | 13.033819 |
| 16242 | 2980 | 12 | 2022-08-20 | 128 | Facebook Ads | 3200 | 300.0 | 250.0 | #Be Bold. Be X | 90.0 | https://www.abcjewelry.com/women/minimalist-je... | 5755 | Malad | 353 | 10000.0 | INR | 8 | Social | 0.0 | 4322.588613 | 11080 | 528 | INR | INR | 1 | 94.714367 | Others | INR | Asia/Kolkata | week_day | spring jewelry | 4.765343 |
| 16227 | 2980 | 29 | 2022-09-06 | 4 | Google Ads | 3195 | 300.0 | 250.0 | #The X Factor - Fashion for the Fearless | 90.0 | https://www.abcjewelry.com/collections/bold-je... | 5755 | Malad | 353 | 10000.0 | INR | 1 | Display | 0.0 | 4322.588613 | 6610 | 528 | INR | INR | 1 | 49.312327 | Others | INR | Asia/Kolkata | week_day | funky jewelry | 7.987897 |
| 22371 | 3134 | 24 | 2022-09-11 | 16 | DV360 | 4940 | 300.0 | 250.0 | #The X Factor - Fashion for the Fearless | 90.0 | https://www.abcjewelry.com/women/costume-jewelry/ | 6319 | Orissa | 353 | 415000.0 | INR | 4 | Search | 0.0 | 1245.019920 | 4095 | 528 | INR | INR | 1 | 39.160444 | Others | INR | Asia/Kolkata | week_end | statement necklaces | 12.893773 |
| 20349 | 3107 | 9 | 2022-10-07 | 16 | DV360 | 4350 | 300.0 | 250.0 | #Embrace Your Individuality with X | 90.0 | https://www.abcjewelry.com/women/boho-jewelry/ | 6334 | Thailand | 353 | 295000.0 | INR | 1 | Display | 0.0 | 3735.059761 | 9829 | 528 | INR | INR | 1 | 143.196029 | Others | INR | Asia/Kolkata | week_day | delicate bracelets | 5.371859 |
| 20566 | 3129 | 16 | 2022-08-21 | 4 | Google Ads | 4415 | 300.0 | 250.0 | #Embrace Your Individuality with X | 90.0 | https://www.abcjewelry.com/collections/vintage... | 6334 | Thailand | 353 | 1010000.0 | INR | 1 | Display | 0.0 | 2056.772908 | 11731 | 527 | INR | INR | 1 | 96.560533 | Others | INR | Asia/Kolkata | week_end | religious jewelry | 4.492371 |
| 70476 | 3058 | 23 | 2022-10-23 | 16 | DV360 | 14310 | 0.0 | 0.0 | #Be Bold. Be X | -1.0 | https://www.abcjewelry.com/collections | 6328 | Madhya Pradesh | 353 | 115000.0 | INR | 1 | Display | 0.0 | 3735.059761 | 10506 | 527 | INR | INR | 1 | 117.851509 | Others | INR | Asia/Kolkata | week_end | cuff bracelets | 5.016181 |
| 19614 | 3058 | 2 | 2022-09-30 | 4 | Google Ads | 4135 | 300.0 | 250.0 | #Timeless X Style | 90.0 | https://www.abcjewelry.com/collections/dainty-... | 6319 | Orissa | 353 | 215000.0 | INR | 4 | Search | 0.0 | 1494.023904 | 7646 | 526 | INR | INR | 1 | 74.726546 | Others | INR | Asia/Kolkata | week_day | hoop earrings | 6.879414 |
| 22480 | 3134 | 5 | 2022-08-30 | 128 | Facebook Ads | 4980 | 300.0 | 250.0 | #Timeless X Style | 90.0 | https://www.abcjewelry.com/collections/vintage... | 6319 | Orissa | 353 | 415000.0 | INR | 1 | Display | 0.0 | 1359.561753 | 13387 | 526 | INR | INR | 1 | 102.737796 | Others | INR | Asia/Kolkata | week_day | concert jewelry | 3.929185 |
| 17746 | 3053 | 12 | 2022-09-06 | 4 | Google Ads | 3625 | 300.0 | 250.0 | #The Power of X | 90.0 | https://www.abcjewelry.com/women/handmade-jewe... | 6337 | Kuala Lumpur | 353 | 1015000.0 | INR | 32 | Mobile | 0.0 | 1222.609562 | 15259 | 526 | INR | INR | 1 | 84.628406 | Others | INR | Asia/Kolkata | week_day | handcrafted jewelry | 3.447146 |
| 72578 | 3563 | 16 | 2022-12-03 | 128 | Facebook Ads | 15595 | 0.0 | 0.0 | #Be Bold. Be X | -1.0 | https://www.abcjewelry.com/collections/stateme... | 6334 | Thailand | 353 | 1905942.0 | INR | 8 | Social | 0.0 | 2085.122041 | 10115 | 525 | INR | INR | 1 | 88.484745 | Others | INR | Asia/Kolkata | week_day | body piercings | 5.190311 |
| 21350 | 3128 | 3 | 2022-08-28 | 16 | DV360 | 4645 | 300.0 | 250.0 | #The Power of X | 90.0 | https://www.abcjewelry.com/collections/layered... | 6331 | Tamil Nadu | 353 | 870000.0 | INR | 1 | Display | 0.0 | 1973.356574 | 19197 | 525 | INR | INR | 1 | 150.480855 | Others | INR | Asia/Kolkata | week_end | vibrant jewelry | 2.734802 |
| 16535 | 3035 | 11 | 2022-10-09 | 16 | DV360 | 3285 | 300.0 | 250.0 | #The X Factor - Fashion for the Fearless | 90.0 | https://www.abcjewelry.com/collections/unique-... | 6292 | Andhra Pradesh | 353 | 205000.0 | INR | 1 | Display | 0.0 | 2490.039841 | 7874 | 524 | INR | INR | 1 | 96.970581 | Others | INR | Asia/Kolkata | week_end | statement pins | 6.654813 |
| 16539 | 3035 | 15 | 2022-10-13 | 16 | DV360 | 3285 | 300.0 | 250.0 | #Be Bold. Be X | 90.0 | https://www.abcjewelry.com/collections/beaded-... | 6292 | Andhra Pradesh | 353 | 205000.0 | INR | 8 | Social | 0.0 | 2490.039841 | 9067 | 524 | INR | INR | 1 | 97.144440 | Others | INR | Asia/Kolkata | week_day | layered bracelets | 5.779199 |
| 19215 | 3054 | 13 | 2022-09-05 | 16 | DV360 | 4030 | 300.0 | 250.0 | #The X Factor - Fashion for the Fearless | 93.0 | https://www.abcjewelry.com/women/boho-jewelry/ | 5641 | Karnataka | 353 | 5000.0 | INR | 64 | Video | 0.0 | 560.258964 | 8189 | 524 | INR | INR | 1 | 74.654203 | Others | INR | Asia/Kolkata | week_day | vintage-inspired jewelry | 6.398828 |
| 70618 | 3099 | 19 | 2022-08-24 | 4 | Google Ads | 14350 | 0.0 | 0.0 | #Embrace Your Individuality with X | -1.0 | https://www.abcjewelry.com/women/minimalist-je... | 6370 | North | 353 | 100000.0 | INR | 8 | Social | 0.0 | 1844.480158 | 14877 | 523 | INR | INR | 1 | 98.331425 | Others | INR | Asia/Kolkata | week_day | affordable statement jewelry | 3.515494 |
| 20611 | 3129 | 21 | 2022-08-26 | 128 | Facebook Ads | 4425 | 300.0 | 250.0 | #Be Bold. Be X | 90.0 | https://www.abcjewelry.com/collections/dainty-... | 6334 | Thailand | 353 | 1010000.0 | INR | 64 | Video | 0.0 | 2025.560259 | 13329 | 522 | INR | INR | 1 | 120.078141 | Others | INR | Asia/Kolkata | week_day | winter jewelry | 3.916273 |
| 19710 | 3099 | 2 | 2022-07-06 | 128 | Facebook Ads | 4160 | 300.0 | 250.0 | #Embrace Your Individuality with X | 90.0 | https://www.abcjewelry.com/collections/pendant... | 6370 | North | 353 | 100000.0 | INR | 8 | Social | 0.0 | 1854.370132 | 20199 | 522 | INR | INR | 1 | 166.163153 | Others | INR | Asia/Kolkata | week_day | zodiac jewelry | 2.584286 |
| 19713 | 3099 | 5 | 2022-07-09 | 4 | Google Ads | 4160 | 300.0 | 250.0 | #Timeless X Style | 90.0 | https://www.abcjewelry.com/collections/stateme... | 6370 | North | 353 | 100000.0 | INR | 4 | Search | 0.0 | 1854.370132 | 18626 | 521 | INR | INR | 1 | 166.078852 | Others | INR | Asia/Kolkata | week_day | body jewelry | 2.797165 |
| 17459 | 3052 | 24 | 2022-08-29 | 128 | Facebook Ads | 3535 | 300.0 | 250.0 | #Be Bold. Be X | 90.0 | https://www.abcjewelry.com/collections/stateme... | 6337 | Kuala Lumpur | 353 | 1015000.0 | INR | 4 | Search | 0.0 | 2000.747012 | 10987 | 521 | INR | INR | 1 | 118.084605 | Others | INR | Asia/Kolkata | week_day | victorian jewelry | 4.741968 |
| 21104 | 3128 | 22 | 2022-08-27 | 128 | Facebook Ads | 4560 | 300.0 | 250.0 | #Embrace Your Individuality with X | 90.0 | https://www.abcjewelry.com/women/designer-insp... | 6331 | Tamil Nadu | 353 | 870000.0 | INR | 1 | Display | 0.0 | 2000.747012 | 14665 | 520 | INR | INR | 1 | 115.597048 | Others | INR | Asia/Kolkata | week_day | personalized jewelry | 3.545857 |
| 22225 | 3133 | 17 | 2022-08-22 | 128 | Facebook Ads | 4885 | 300.0 | 250.0 | #Embrace Your Individuality with X | 90.0 | https://www.abcjewelry.com/collections/bold-je... | 6319 | Orissa | 353 | 415000.0 | INR | 8 | Social | 0.0 | 1146.663347 | 10153 | 520 | INR | INR | 1 | 71.956222 | Others | INR | Asia/Kolkata | week_day | beaded jewelry | 5.121639 |
| 69626 | 3048 | 5 | 2022-10-05 | 4 | Google Ads | 13950 | 0.0 | 0.0 | #Timeless X Style | -1.0 | https://www.abcjewelry.com/women/bold-jewelry/ | 6325 | Chandpur | 353 | 85000.0 | INR | 32 | Mobile | 0.0 | 2490.039841 | 6645 | 520 | INR | INR | 1 | 93.972719 | Others | INR | Asia/Kolkata | week_day | victorian jewelry | 7.825433 |
| 22175 | 3133 | 31 | 2022-08-26 | 16 | DV360 | 4875 | 300.0 | 250.0 | #Embrace Your Individuality with X | 90.0 | https://www.abcjewelry.com/collections/beaded-... | 6319 | Orissa | 353 | 415000.0 | INR | 32 | Mobile | 0.0 | 1020.916335 | 10339 | 519 | INR | INR | 1 | 61.046414 | Others | INR | Asia/Kolkata | week_day | hair accessories | 5.019828 |
| 15826 | 2949 | 14 | 2022-11-10 | 4 | Google Ads | 3055 | 300.0 | 250.0 | #The Ultimate Fashion Statement with X | 90.0 | https://www.abcjewelry.com/collections/delicat... | 5908 | Addis Ababa | 353 | 1000.0 | INR | 8 | Social | 0.0 | 1228.652169 | 4723 | 519 | INR | INR | 1 | 30.282426 | Others | INR | Asia/Kolkata | week_day | pearl jewelry | 10.988778 |
| 72584 | 3563 | 22 | 2022-12-09 | 4 | Google Ads | 15595 | 0.0 | 0.0 | #Timeless X Style | -1.0 | https://www.abcjewelry.com/collections/chunky-... | 6334 | Thailand | 353 | 1905942.0 | INR | 32 | Mobile | 0.0 | 2085.122041 | 10296 | 519 | INR | INR | 1 | 91.953161 | Others | INR | Asia/Kolkata | week_day | layered jewelry | 5.040793 |
| 20584 | 3130 | 27 | 2022-09-14 | 4 | Google Ads | 4420 | 300.0 | 250.0 | #The Power of X | 90.0 | https://www.abcjewelry.com/collections/gemston... | 6334 | Thailand | 353 | 1010000.0 | INR | 1 | Display | 0.0 | 2241.035857 | 12191 | 518 | INR | INR | 1 | 74.802909 | Others | INR | Asia/Kolkata | week_day | ear cuffs | 4.249036 |
| 16310 | 2982 | 4 | 2022-08-16 | 4 | Google Ads | 3220 | 300.0 | 250.0 | #Embrace Your Individuality with X | 90.0 | https://www.abcjewelry.com/women/minimalist-je... | 6019 | Indore | 353 | 15000.0 | INR | 4 | Search | 0.0 | 2470.050636 | 14556 | 518 | INR | INR | 1 | 90.729686 | Others | INR | Asia/Kolkata | week_day | initial jewelry | 3.558670 |
| 18731 | 3048 | 16 | 2022-10-14 | 4 | Google Ads | 3890 | 300.0 | 250.0 | #The Power of X | 90.0 | https://www.abcjewelry.com/collections/minimal... | 6298 | Gujarat | 353 | 0.0 | INR | 8 | Social | 0.0 | 1867.529880 | 7498 | 518 | INR | INR | 1 | 71.824478 | Others | INR | Asia/Kolkata | week_day | midi rings | 6.908509 |
| 70391 | 3055 | 6 | 2022-10-27 | 128 | Facebook Ads | 14275 | 0.0 | 0.0 | #Embrace Your Individuality with X | -1.0 | https://www.abcjewelry.com/women/vintage-inspi... | 6331 | Tamil Nadu | 353 | 268786.0 | INR | 1 | Display | 0.0 | 1245.019920 | 9802 | 518 | INR | INR | 1 | 74.573195 | Others | INR | Asia/Kolkata | week_day | baroque jewelry | 5.284636 |
| 70619 | 3099 | 20 | 2022-08-25 | 16 | DV360 | 14350 | 0.0 | 0.0 | #The Power of X | -1.0 | https://www.abcjewelry.com/collections/rhinest... | 6370 | North | 353 | 100000.0 | INR | 4 | Search | 0.0 | 1844.480158 | 13638 | 518 | INR | INR | 1 | 98.634677 | Others | INR | Asia/Kolkata | week_day | art nouveau jewelry | 3.798211 |
| 21536 | 3131 | 10 | 2022-07-14 | 4 | Google Ads | 4695 | 300.0 | 250.0 | #The Power of X | 90.0 | https://www.abcjewelry.com/collections/trendy-... | 5686 | Maharashtra | 353 | 1015000.0 | INR | 8 | Social | 0.0 | 3735.059761 | 26542 | 517 | INR | INR | 1 | 270.690742 | Others | INR | Asia/Kolkata | week_day | fashion jewelry for women | 1.947856 |
| 71298 | 3130 | 10 | 2022-08-28 | 16 | DV360 | 14680 | 0.0 | 0.0 | #The Ultimate Fashion Statement with X | -1.0 | https://www.abcjewelry.com/collections/layered... | 6334 | Thailand | 353 | 1010000.0 | INR | 32 | Mobile | 0.0 | 2241.035857 | 10895 | 517 | INR | INR | 1 | 74.266140 | Others | INR | Asia/Kolkata | week_end | bold jewelry | 4.745296 |
| 21353 | 3128 | 6 | 2022-08-31 | 4 | Google Ads | 4645 | 300.0 | 250.0 | #Be Bold. Be X | 90.0 | https://www.abcjewelry.com/collections/tassel-... | 6331 | Tamil Nadu | 353 | 870000.0 | INR | 32 | Mobile | 0.0 | 1973.356574 | 18903 | 516 | INR | INR | 1 | 148.106892 | Others | INR | Asia/Kolkata | week_day | seasonal jewelry | 2.729725 |
| 70315 | 3054 | 10 | 2022-08-28 | 16 | DV360 | 14240 | 0.0 | 0.0 | #The Ultimate Fashion Statement with X | -1.0 | https://www.abcjewelry.com/collections/mixed-m... | 5641 | Karnataka | 353 | 5000.0 | INR | 64 | Video | 0.0 | 2241.035857 | 9340 | 515 | INR | INR | 1 | 74.214514 | Others | INR | Asia/Kolkata | week_end | unique jewelry | 5.513919 |
| 20210 | 3103 | 16 | 2022-07-02 | 16 | DV360 | 4320 | 300.0 | 250.0 | #Timeless X Style | 90.0 | https://www.abcjewelry.com/collections/stateme... | 6370 | North | 353 | 100000.0 | INR | 32 | Mobile | 0.0 | 3090.616887 | 15189 | 514 | INR | INR | 1 | 105.631295 | Others | INR | Asia/Kolkata | week_day | bangles | 3.384028 |
| 16639 | 3039 | 20 | 2022-07-25 | 16 | DV360 | 3310 | 300.0 | 250.0 | #The X Factor - Fashion for the Fearless | 90.0 | https://www.abcjewelry.com/collections/pearl-j... | 6292 | Andhra Pradesh | 353 | 905000.0 | INR | 32 | Mobile | 0.0 | 2490.039841 | 15922 | 514 | INR | INR | 1 | 203.030094 | Others | INR | Asia/Kolkata | week_day | beach jewelry | 3.228238 |
| 70470 | 3058 | 17 | 2022-10-17 | 4 | Google Ads | 14310 | 0.0 | 0.0 | #The Power of X | -1.0 | https://www.abcjewelry.com/collections/designe... | 6328 | Madhya Pradesh | 353 | 115000.0 | INR | 1 | Display | 0.0 | 3735.059761 | 10992 | 514 | INR | INR | 1 | 123.582821 | Others | INR | Asia/Kolkata | week_day | beaded jewelry | 4.676128 |
| 19406 | 3054 | 2 | 2022-09-08 | 4 | Google Ads | 4085 | 300.0 | 250.0 | #Be Bold. Be X | 90.0 | https://www.abcjewelry.com/collections/hoop-ea... | 5641 | Karnataka | 353 | 5000.0 | INR | 32 | Mobile | 0.0 | 622.509960 | 7057 | 514 | INR | INR | 1 | 31.943065 | Others | INR | Asia/Kolkata | week_day | art nouveau jewelry | 7.283548 |
| 17976 | 3056 | 18 | 2022-10-16 | 4 | Google Ads | 3690 | 300.0 | 250.0 | #Be Bold. Be X | 90.0 | https://www.abcjewelry.com/women/handmade-jewe... | 5686 | Maharashtra | 353 | 1015000.0 | INR | 4 | Search | 0.0 | 3112.549801 | 9019 | 514 | INR | INR | 1 | 120.160485 | Others | INR | Asia/Kolkata | week_end | bold jewelry | 5.699080 |
| 21627 | 3131 | 22 | 2022-08-27 | 4 | Google Ads | 4715 | 300.0 | 250.0 | #Be Bold. Be X | 90.0 | https://www.abcjewelry.com/collections/multi-s... | 5686 | Maharashtra | 353 | 1015000.0 | INR | 1 | Display | 0.0 | 1682.021912 | 16390 | 513 | INR | INR | 1 | 87.098777 | Others | INR | Asia/Kolkata | week_day | minimalist jewelry | 3.129957 |
| 20580 | 3130 | 23 | 2022-09-10 | 128 | Facebook Ads | 4420 | 300.0 | 250.0 | #The Ultimate Fashion Statement with X | 90.0 | https://www.abcjewelry.com/collections/long-ne... | 6334 | Thailand | 353 | 1010000.0 | INR | 64 | Video | 0.0 | 2241.035857 | 9774 | 512 | INR | INR | 1 | 74.411266 | Others | INR | Asia/Kolkata | week_day | multi-strand necklaces | 5.238388 |
| 69851 | 3050 | 24 | 2022-10-25 | 128 | Facebook Ads | 14015 | 0.0 | 0.0 | #Embrace Your Individuality with X | -1.0 | https://www.abcjewelry.com/collections/trendy-... | 5641 | Karnataka | 353 | 805000.0 | INR | 64 | Video | 0.0 | 622.509960 | 3582 | 512 | INR | INR | 1 | 24.563921 | Others | INR | Asia/Kolkata | week_day | victorian jewelry | 14.293691 |
| 18954 | 3051 | 10 | 2022-09-02 | 4 | Google Ads | 3955 | 300.0 | 250.0 | #Be Bold. Be X | 93.0 | https://www.abcjewelry.com/women/statement-jew... | 5641 | Karnataka | 353 | 5000.0 | INR | 8 | Social | 0.0 | 441.982072 | 6444 | 512 | INR | INR | 1 | 66.835769 | Retargeting | INR | Asia/Kolkata | week_day | minimalist jewelry | 7.945376 |
| 19715 | 3099 | 7 | 2022-07-11 | 4 | Google Ads | 4160 | 300.0 | 250.0 | #The X Factor - Fashion for the Fearless | 90.0 | https://www.abcjewelry.com/collections | 6370 | North | 353 | 100000.0 | INR | 32 | Mobile | 0.0 | 1854.370132 | 18685 | 512 | INR | INR | 1 | 165.776948 | Others | INR | Asia/Kolkata | week_day | bridal jewelry | 2.740166 |
| 19040 | 3053 | 28 | 2022-08-20 | 128 | Facebook Ads | 3980 | 300.0 | 250.0 | #The Ultimate Fashion Statement with X | 90.0 | https://www.abcjewelry.com/collections | 5641 | Karnataka | 353 | 5000.0 | INR | 32 | Mobile | 0.0 | 1245.019920 | 8744 | 511 | INR | INR | 1 | 56.825992 | Others | INR | Asia/Kolkata | week_day | fashionable jewelry | 5.844007 |
| 17483 | 3052 | 21 | 2022-08-26 | 128 | Facebook Ads | 3540 | 300.0 | 250.0 | #Be Bold. Be X | 90.0 | https://www.abcjewelry.com/women/costume-jewelry/ | 6337 | Kuala Lumpur | 353 | 1015000.0 | INR | 1 | Display | 0.0 | 2019.422311 | 14057 | 511 | INR | INR | 1 | 118.946097 | Others | INR | Asia/Kolkata | week_day | handmade jewelry | 3.635200 |
| 70468 | 3058 | 15 | 2022-10-15 | 128 | Facebook Ads | 14310 | 0.0 | 0.0 | #Be Bold. Be X | -1.0 | https://www.abcjewelry.com/collections/handmad... | 6328 | Madhya Pradesh | 353 | 115000.0 | INR | 32 | Mobile | 0.0 | 3735.059761 | 11218 | 511 | INR | INR | 1 | 126.246230 | Others | INR | Asia/Kolkata | week_day | costume jewelry sets | 4.555179 |
| 70616 | 3099 | 17 | 2022-08-22 | 128 | Facebook Ads | 14350 | 0.0 | 0.0 | #Be Bold. Be X | -1.0 | https://www.abcjewelry.com/collections/pearl-j... | 6370 | North | 353 | 100000.0 | INR | 32 | Mobile | 0.0 | 1844.480158 | 13403 | 511 | INR | INR | 1 | 89.222825 | Others | INR | Asia/Kolkata | week_day | anklets | 3.812579 |
| 17448 | 3052 | 13 | 2022-08-18 | 16 | DV360 | 3535 | 300.0 | 250.0 | #The X Factor - Fashion for the Fearless | 90.0 | https://www.abcjewelry.com/women/costume-jewelry/ | 6337 | Kuala Lumpur | 353 | 1015000.0 | INR | 64 | Video | 0.0 | 2000.747012 | 12408 | 510 | INR | INR | 1 | 88.701568 | Others | INR | Asia/Kolkata | week_day | designer-inspired jewelry | 4.110251 |
df['ext_service_name'].value_counts()
Facebook Ads 24275 DV360 24171 Google Ads 24166 Name: ext_service_name, dtype: int64
Reasonings are conveyed at the chart descriptions
A histogram is used to illustrate the distribution of a dataset and displays which values are most frequent.
Reasons
The benchmarks for CTR ( Click through rate ) is 0.76% for Style & Fashion tags (Google) and 2.71% to be in Top 10% competition. Our CTR distribution lies between 0.76-2.71 for ABC company.
# Clicks per 100 Appearances Frequency Distribution
# set the xlabel and the fontsize
plt.xlabel("ctr", fontsize=15)
# set the ylabel and the fontsize
plt.ylabel("Frequency", fontsize=15)
# set the title of the plot
plt.title("Clicks per 100 Appearances Distribution", fontsize=15)
# plot the histogram for the target variable
plt.hist(df.loc[(df["ctr"]>=0.1) & (df["ctr"]<=0.76)]['ctr'])
plt.show()
# Filter the data and create a histogram
hist_data = df.loc[(df["ctr"]>0.1) & (df["ctr"]<=3.71)]['ctr']
fig = px.histogram(hist_data, nbins=20)
# Set the layout properties
fig.update_layout(
title="Clicks per 100 Appearances Distribution",
xaxis_title="cpa",
yaxis_title="Frequency",
font=dict(size=15),
showlegend=False, width=500, # set width to 500 pixels
height=250, # set height to 500 pixels
)
fig.show()
As campaign length increases, impressions and clicks decrease
Campaigns of longer duration have constant & low impressions and clicks
The graphs below can show that most campaigns with less duration were newly created or paused due to poor performance.
We can spot outliers in both the graphs which indicates sudden spikes in the impressions and clicks which maybe due to certain events such as festivals , social media popularity , etc . We can further analyse at what time of the day , on which days , in which season , festivals , national or public holidays the performance usually goes up.
import plotly.express as px
fig = px.scatter(df, x="no_of_days", y="impressions", trendline="ols", width=750, # set width to 500 pixels
height=500, # set height to 500 pixels
)
fig.show()
fig = px.scatter(df, x="no_of_days", y="clicks", trendline="ols", width=750, # set width to 500 pixels
height=500, # set height to 500 pixels
)
fig.show()
Conclusions¶# scatter plot : impressions vs clicks ( hue : channel_name )
sns.lmplot(x = "impressions", y = "clicks", data = df, fit_reg=False, hue='channel_name')
<seaborn.axisgrid.FacetGrid at 0x7f80e60ecf40>
fig = px.scatter(df, x="impressions", y="clicks", color="channel_name",
hover_name="channel_name",
labels={"impressions": "Impressions", "clicks": "Clicks"},
title="Impressions vs Clicks by Channel Name")
fig.update_layout(
font=dict(size=14),
legend=dict(title=None),
plot_bgcolor="white",
margin=dict(l=80, r=20, t=60, b=80),
width=750, # set width to 500 pixels
height=500, # set height to 500 pixels
)
fig.show()
Conclusions¶We need to group Indian states as one country.
# calculate data
labels=df['advertiser_name'].value_counts().index,
values=df['advertiser_name'].value_counts(),
df_abridged=df
df.head()
| campaign_item_id | no_of_days | time | ext_service_id | ext_service_name | creative_id | creative_width | creative_height | search_tags | template_id | landing_page | advertiser_id | advertiser_name | network_id | approved_budget | advertiser_currency | channel_id | channel_name | network_margin | campaign_budget_usd | impressions | clicks | stats_currency | currency_code | exchange_rate | media_cost_usd | search_tag_cat | cmi_currency_code | timezone | weekday_cat | keywords | ctr | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2733 | 7 | 2022-05-01 | 128 | Facebook Ads | 1000 | 300.0 | 250.0 | #The Power of X | 90.0 | https://www.abcjewelry.com/collections/boho-je... | 4756 | Web | 190 | 400.0 | SGD | 32 | Mobile | 0.0 | 652.173913 | 837 | 8 | SGD | SGD | 1 | 14.058514 | Others | SGD | Asia/Singapore | week_end | delicate bracelets | 0.955795 |
| 1 | 2733 | 8 | 2022-05-02 | 16 | DV360 | 1000 | 300.0 | 250.0 | #Be Bold. Be X | 90.0 | https://www.abcjewelry.com/women/costume-jewelry/ | 4756 | Web | 190 | 400.0 | SGD | 8 | Social | 0.0 | 652.173913 | 2634 | 44 | SGD | SGD | 1 | 99.633496 | Others | SGD | Asia/Singapore | week_day | summer jewelry | 1.670463 |
| 2 | 2733 | 9 | 2022-05-03 | 128 | Facebook Ads | 1000 | 300.0 | 250.0 | #Embrace Your Individuality with X | 90.0 | https://www.abcjewelry.com/collections/boho-je... | 4756 | Web | 190 | 400.0 | SGD | 8 | Social | 0.0 | 652.173913 | 2135 | 32 | SGD | SGD | 1 | 109.419677 | Others | SGD | Asia/Singapore | week_day | artisan jewelry | 1.498829 |
| 3 | 2733 | 10 | 2022-05-04 | 128 | Facebook Ads | 1000 | 300.0 | 250.0 | #Be Bold. Be X | 90.0 | https://www.abcjewelry.com/collections/rhinest... | 4756 | Web | 190 | 400.0 | SGD | 64 | Video | 0.0 | 652.173913 | 2327 | 48 | SGD | SGD | 1 | 115.209499 | Others | SGD | Asia/Singapore | week_day | layered bracelets | 2.062742 |
| 4 | 2733 | 11 | 2022-05-05 | 4 | Google Ads | 1000 | 300.0 | 250.0 | #Be Bold. Be X | 90.0 | https://www.abcjewelry.com/collections/boho-je... | 4756 | Web | 190 | 400.0 | SGD | 32 | Mobile | 0.0 | 652.173913 | 1538 | 20 | SGD | SGD | 1 | 66.990104 | Others | SGD | Asia/Singapore | week_day | minimalist jewelry | 1.300390 |
India = ['Andhra Pradesh', 'Karnataka', 'Pan India','North', 'Gujarat', 'Orissa', 'Tamil Nadu', 'Maharashtra', 'West Bengal', 'Madhya Pradesh', 'Coimbatore', 'Bangalore', 'Chennai', 'Punjab', 'Delhi', 'Haryana', 'UP','Pali', 'Vadodara']
Oman = ['Muscat','Sohar']
Qatar = ['Doha']
UAE = ['Dubai', 'Abu Dhabi']
Bahrain = ['Manama']
Kuwait = ['Kuwait City', 'Al Ahmadi']
KSA = ['Jeddah']
Malaysia = ['Kuala Lumpur']
Singapore = ['Singapore']
USA = ['New York']
Thailand = ['Bangkok']
Egypt = ['Cairo', 'Luxor', 'Almaza Bay']
Bangladesh = ['Chattogram', 'Chandpur']
Ethiopia = ['Addis Ababa']
cities_by_country = {'India': India,
'Oman': Oman,
'Qatar': Qatar,
'UAE': UAE,
'Bahrain': Bahrain,
'Kuwait': Kuwait,
'KSA': KSA,
'Malaysia': Malaysia,
'Singapore': Singapore,
'USA': USA,
'Thailand': Thailand,
'Egypt': Egypt,
'Bangladesh': Bangladesh,
'Ethiopia': Ethiopia}
for country,cities in cities_by_country.items():
df.loc[df['advertiser_name'].isin(cities), "advertiser_name"] = country
label = df['advertiser_name'].value_counts().index
name = df['advertiser_name'].value_counts()
# Create data for the Pie Chart
data = [go.Pie(labels=name.index,
values=name,
hole=0.4,
textposition='inside',
textinfo='label+percent',
hoverinfo='label+percent+value')]
# Set layout for the Pie Chart
layout = go.Layout(title='Ad Campaigns running accross the globe (Percentage)',
showlegend=False,
legend=dict(orientation="h"),
width=1000,
height=500,)
# Create figure object
fig = go.Figure(data=data, layout=layout)
# Display the figure
pyo.iplot(fig)
data_2=df.groupby('advertiser_name')['clicks'].sum().sort_values()
# Create data for the Pie Chart
data = [go.Pie(labels=data_2.index,
values=data_2,
hole=0.4,
textposition='inside',
textinfo='label+percent',
hoverinfo='label+percent+value')]
# Set layout for the Pie Chart
layout = go.Layout(title='Clicks across the Globe (%)',
showlegend=False,
width=1000,
height=500,)
# Create figure object
fig = go.Figure(data=data, layout=layout)
# Display the figure
pyo.iplot(fig)
data_3=df.groupby('advertiser_name')['impressions'].sum().sort_values()
# Create data for the Pie Chart
data = [go.Pie(labels=data_3.index,
values=data_3,
hole=0.4,
textposition='inside',
textinfo='label+percent',
hoverinfo='label+percent+value')]
# Set layout for the Pie Chart
layout = go.Layout(title='Ad Appearances across the Globe (%)',
showlegend=False,
width=1000,
height=500,)
# Create figure object
fig = go.Figure(data=data, layout=layout)
# Display the figure
pyo.iplot(fig)
data_4=df.groupby('advertiser_name')['ctr'].mean().sort_values()
data_4
advertiser_name USA 1.159128 Singapore 1.207385 Qatar 1.236015 UAE 1.249283 Bahrain 1.262052 KSA 1.275042 Kuwait 1.294600 Oman 1.334730 Brand 1.384189 Web 1.497644 Egypt 1.685583 Malaysia 1.723401 Indore 2.397669 Bangladesh 2.948884 Thailand 3.792935 India 4.556899 Ethiopia 5.936385 Malad 24.718518 Name: ctr, dtype: float64